/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.SeasonsDTO;
import east2west.interfaces.SeasonInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author thanhluan
 */
public class SeasonDAO implements SeasonInterface {

    Connection con = null;
    CallableStatement call = null;
    SeasonsDTO dto = null;
    ResultSet rs = null;

    public ArrayList<SeasonsDTO> findByAll() {
        ArrayList<SeasonsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Function_GetAll}");
            rs = call.executeQuery();
            list = new ArrayList<SeasonsDTO>();
            while (rs.next()) {
                dto = new SeasonsDTO();
                dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                Date dateEnd = rs.getDate(4);
                if (dateEnd == null) {
                    dto.setEndDate("");
                } else {
                    dto.setEndDate(DateTimeUtil.convert2String(dateEnd, "dd/MM/yyyy"));
                }

                dto.setIsDelete(rs.getByte(5));
                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public SeasonsDTO readById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Seasons_FindId(?)}");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                dto = new SeasonsDTO();
                dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                Date dateEnd = rs.getDate(4);
                if (dateEnd == null) {
                    dto.setEndDate("");
                } else {
                    dto.setEndDate(DateTimeUtil.convert2String(dateEnd, "dd/MM/yyyy"));
                }
                dto.setIsDelete(rs.getByte(5));
            }
            rs.close();
            call.close();
            return dto;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SeasonsDTO> findByName(String name) {
        ArrayList<SeasonsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Seasons_FindName(?)}");
            call.setString(1, name);

            rs = call.executeQuery();
            list = new ArrayList<SeasonsDTO>();
            while (rs.next()) {
                dto = new SeasonsDTO();
                dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                Date dateEnd = rs.getDate(4);
                if (dateEnd == null) {
                    dto.setEndDate("");
                } else {
                    dto.setEndDate(DateTimeUtil.convert2String(dateEnd, "dd/MM/yyyy"));
                }
                dto.setIsDelete(rs.getByte(5));

                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SeasonsDTO> findByStarDate(String Stdate) {
        ArrayList<SeasonsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Seasons_FindStarDate(?)}");
            call.setString(1, Stdate);

            rs = call.executeQuery();
            list = new ArrayList<SeasonsDTO>();
            while (rs.next()) {
                dto = new SeasonsDTO();
                dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                Date dateEnd = rs.getDate(4);
                if (dateEnd == null) {
                    dto.setEndDate("");
                } else {
                    dto.setEndDate(DateTimeUtil.convert2String(dateEnd, "dd/MM/yyyy"));
                }
                dto.setIsDelete(rs.getByte(5));

                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SeasonsDTO> findByEndDate(String Endate) {
        ArrayList<SeasonsDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("{call sp_Call_V_Seasons_FindStarEnd(?)}");
            call.setString(1, Endate);

            rs = call.executeQuery();
            list = new ArrayList<SeasonsDTO>();
            while (rs.next()) {
                dto = new SeasonsDTO();
                dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                Date date = rs.getDate(3);
                if (date == null) {
                    dto.setStartDate("");
                } else {
                    dto.setStartDate(DateTimeUtil.convert2String(date, "dd/MM/yyyy"));
                }
                Date dateEnd = rs.getDate(4);
                if (dateEnd == null) {
                    dto.setEndDate("");
                } else {
                    dto.setEndDate(DateTimeUtil.convert2String(dateEnd, "dd/MM/yyyy"));
                }
                dto.setIsDelete(rs.getByte(5));

                list.add(dto);
            }
            rs.close();
            call.close();
            return list;
        } catch (SQLException ex) {

            return null;
        }

    }

    public ArrayList<SeasonsDTO> findByDTO(SeasonsDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean create(SeasonsDTO season) {
        try {
            season = new SeasonsDTO();
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Insert_Seasons(?,?,?,?)}");
            call.setString(1, dto.getSeasonName());
            call.setDate(2, DateTimeUtil.convert2Date(dto.getStartDate(), "dd/MM/yyyy"));
            call.setDate(3, DateTimeUtil.convert2Date(dto.getEndDate(), "dd/MM/yyyy"));
            call.setByte(4, dto.getIsDelete());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean update(SeasonsDTO season) {
        try {
            season = new SeasonsDTO();
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Update_Seasons(?,?,?,?,?)}");
            call.setInt(1, dto.getSeasinId());
            call.setString(2, dto.getSeasonName());
            call.setDate(3, DateTimeUtil.convert2Date(dto.getStartDate(), "dd-MM-yyyy"));
            call.setDate(4, DateTimeUtil.convert2Date(dto.getEndDate(), "dd-MM-yyyy"));
            call.setByte(5, dto.getIsDelete());
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public boolean delete(SeasonsDTO season) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall("{call sp_Delete_Seasons(?)}");
            call.setInt(1, id);
            call.executeUpdate();
            call.close();
            return true;
        } catch (SQLException ex) {
            try {

                if (con != null) {
                    con.rollback();
                }

            } catch (SQLException ex1) {
            }
            return false;
        } finally {
            try {
                if (con != null && !con.isClosed()) {
                    DatabaseUtil.closeConnection(con);
                }
            } catch (SQLException ex) {
            }
        }
    }

    public ArrayList<SeasonsDTO> findBySeasonName() {
       
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT SeasonId,SeasonName,month(StartDate) as 'Start',month(EndDate) as 'End' From V_Season");
            rs = call.executeQuery();
            ArrayList<SeasonsDTO> list = new ArrayList<SeasonsDTO>();
            while (rs.next()) {
                dto = new SeasonsDTO();
      dto.setSeasinId(rs.getInt(1));
                dto.setSeasonName(rs.getString(2));
                dto.setStart(rs.getInt("Start"));
                dto.setEnd(rs.getInt("End"));
          

                list.add(dto);
            }

            return list;
        } catch (SQLException ex) {

            return null;
        }
    }
     public SeasonsDTO readByIdMonth(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("SELECT SeasonName,month(StartDate) as 'Start',month(EndDate) as 'End' From V_Season Where SeasonId=?");
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                 dto = new SeasonsDTO();

                dto.setSeasonName(rs.getString(1));
                dto.setStart(rs.getInt("Start"));
                dto.setEnd(rs.getInt("End"));
               
            }
           
            return dto;
        } catch (SQLException ex) {

            return null;
        }

    }
}
